package com.sf.dlb.mapper.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mapstruct.Mapper;

import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * 图表 Mapper
 *
 * Created by jing.tan on 2018/5/21.
 */
@Mapper
public interface ChartMapper {
    /**
     * 查找收益总数
     * @param delFlag
     * @return
     */
    @Select("select SUM(a.get_number) from new_recod a where a.del_flag=#{delFlag} AND a.get_state=#{getState}")
    Double findByGetDlbTotal(@Param("delFlag") String delFlag,@Param("getState")String getState);
    /**
     * 阅读时长分析
     */
    @Select("select DATE_FORMAT(a.date,'%Y-%m-%d') as \"date\",ifnull(u.timeSum,0) as \"timeSum\" from temp_date a LEFT JOIN (select DATE_FORMAT(a.create_date,'%Y-%m-%d') as \"createDate\",IFNULL(SUM(a.read_time_long),0) as \"timeSum\" from new_recod a where a.create_date>#{startTime} and a.create_date<#{endTime} and a.del_flag=#{delFlag} and a.get_state=#{getState} group by DATE_FORMAT(a.create_date,'%Y-%m-%d')) u on DATE_FORMAT(a.date,'%Y-%m-%d')=DATE_FORMAT(u.createDate,'%Y-%m-%d') where a.date>#{startTime} and a.date<#{endTime} order by a.date")
    List<Map> findByUserReaderTimeReport(@Param("startTime")Date startTime,@Param("endTime")Date endTime, @Param("delFlag") String delFlag,@Param("getState")String getState);
    /**
     * 阅读总时长
     */
    @Select("SELECT SUM(a.read_time_long) AS \"timeSum\" FROM new_recod a WHERE a.del_flag =#{delFlag} and a.get_state=#{getState}")
    Long findByUserReaderTimeReportTotal(@Param("delFlag") String delFlag,@Param("getState")String getState);
    /**
     * APP用户增长分析
     */
    @Select("select DATE_FORMAT(a.date,'%Y-%m-%d') as \"date\",ifnull(u.userCount,0) as \"userCount\" from temp_date a LEFT JOIN (select DATE_FORMAT(a.create_date,'%Y-%m-%d') as \"createDate\",count(a.create_date) as \"userCount\" from user_info a where a.create_date>#{startTime} and a.create_date<#{endTime} and a.del_flag=#{delFlag} group by DATE_FORMAT(a.create_date,'%Y-%m-%d')) u on DATE_FORMAT(a.date,'%Y-%m-%d')=DATE_FORMAT(u.createDate,'%Y-%m-%d') where a.date>#{startTime} and a.date<#{endTime} order by a.date ")
    List<Map> findByUserIncreaseReport(@Param("startTime")Date startTime,@Param("endTime")Date endTime, @Param("delFlag") String delFlag);
    /**
     * 查找用户总数
     * @param delFlag
     * @return
     */
    @Select("select count(*) from user_info a where a.del_flag=#{delFlag}")
    Integer findByUserCount(@Param("delFlag") String delFlag);

    /**
     * 用户喜好分析：阅读的用户量
     */
    @Select("SELECT a.class_id as \"classId\",a.name as \"className\",IFNULL(n.classCount, 0) as \"classCount\" FROM new_class a LEFT JOIN (SELECT a.class_id AS \"classId\",count(a.class_id) AS \"classCount\" FROM new_recod a WHERE a.create_date > #{startTime} AND a.create_date < #{endTime} AND a.del_flag = #{delFlag} AND a.get_state=#{getState} GROUP BY a.class_id ) n ON a.class_id = n.classId and a.del_flag=#{delFlag} ")
    List<Map> findByUserNewClassReport(@Param("startTime")Date startTime,@Param("endTime")Date endTime, @Param("delFlag") String delFlag,@Param("getState")String getState);
    /**
     * 阅读总数
     * @param delFlag
     * @return
     */
    @Select("SELECT count(*) FROM new_recod a where a.del_flag=#{delFlag} AND a.get_state=#{getState}")
    Long findByUserNewClassReportTotal(@Param("delFlag") String delFlag,@Param("getState")String getState);

    /**
     * 用户收益分析:
     */
    @Select("select DATE_FORMAT(a.date,'%Y-%m-%d') as \"date\",ifnull(u.dlbSum,0) as \"dlbSum\" from temp_date a LEFT JOIN (select DATE_FORMAT(a.create_date,'%Y-%m-%d') as \"createDate\",IFNULL(SUM(a.get_number),0) as \"dlbSum\" from new_recod a where a.create_date>#{startTime} and a.create_date<#{endTime} and a.del_flag=#{delFlag} and a.get_state=#{getState} group by DATE_FORMAT(a.create_date,'%Y-%m-%d')) u on DATE_FORMAT(a.date,'%Y-%m-%d')=DATE_FORMAT(u.createDate,'%Y-%m-%d') where a.date>#{startTime} and a.date<#{endTime}  order by a.date")
    List<Map> findByUserGetDlbReport(@Param("startTime")Date startTime,@Param("endTime")Date endTime, @Param("delFlag") String delFlag,@Param("getState")String getState);

    /**
     * DLB发放状态-饼图
     * @return
     */
    @Select("select sum(a.available_dlb) as 'available_dlb',sum(a.unlock_dlb) as 'unlock_dlb',sum(a.lock_dlb) as 'lock_dlb' from user_wallet a")
    Map findByGiveOutDlb();
    /**
     * 未来30天解锁分布图-折线
     * @return
     */
    @Select("select DATE_FORMAT(d.date,'%Y-%m-%d') as 'lock_date',IFNULL(w.lock_count,0) as 'lock_count' from temp_date d LEFT JOIN (select DATE_FORMAT(date_add(a.create_date, interval #{day} day),'%Y-%m-%d') as 'lock_date',sum(a.lock_count) as 'lock_count' from wallet_lock_info a where a.lock_state='3' and a.lock_type='1' and a.del_flag='0' group by DATE_FORMAT(a.create_date,'%Y-%m-%d')) w on DATE_FORMAT(d.date,'%Y-%m-%d')=DATE_FORMAT(w.lock_date,'%Y-%m-%d') where d.date>#{startTime} and d.date<#{endTime} order by d.date")
    List<Map> findByUnlockDistributionAndDay(@Param("startTime") Date startTime, @Param("endTime") Date endTime,@Param("day") int day);
    /**
     * 实名状态-饼图
     * @return
     */
    @Select("select a.state,count(a.state) as 'count' from (select u.user_id,IFNULL(r.state,\"1\") as 'state' from user_info u LEFT JOIN user_real_info r on u.user_id=r.user_id) a group by state")
    List<Map> findByRealState();

}